from sqlalchemy.orm import Session
from .emp_models import Emp
from .emp_schema import Emp_Schema
from xiangmu.student.stu_crud import get_id_stu
from sqlalchemy import func
from datetime import date


# 查询所有就业信息
def get_all_emp(db: Session):
    return db.query(Emp).all()

# 通过学生编号查询
def get_id_emp(db: Session, student_id: int):
    return db.query(Emp).filter(Emp.student_id == student_id).first()  # 补充first()获取单条

# 通过公司名称查询
def get_name_emp(db: Session, company_name: str):
    return db.query(Emp).filter(Emp.company_name == company_name).all()

# 通过工资范围查询
def get_sal_emp(db: Session, min_salary: int, max_salary: int):
    return db.query(Emp).filter(
        Emp.salary >= min_salary,
        Emp.salary <= max_salary  # 修正为Emp.salary
    ).all()

# 添加就业信息
def add_emp(db: Session, emp: Emp_Schema):
    # 先检查学生是否存在
    student = get_id_stu(db, student_id=emp.student_id)
    if not student:
        return {"message": "学生不存在，无法添加就业信息"}

    # 检查就业信息是否已存在
    exist_emp = get_id_emp(db, student_id=emp.student_id)
    if exist_emp:
        return {"message": "学生编号已存在"}

    db_emp = Emp(
        student_id=emp.student_id,
        student_name=emp.student_name,
        class_name=emp.class_name,
        job_search_start_date=emp.job_search_start_date,
        offer_received_date=emp.offer_received_date,
        company_name=emp.company_name,
        salary=emp.salary
    )
    db.add(db_emp)
    db.commit()
    db.refresh(db_emp)
    return {"message": "添加成功", "data": db_emp}

# 修改就业信息
def mod_emp(db: Session, emp_schema: Emp_Schema):
    db_emp = get_id_emp(db, student_id=emp_schema.student_id)
    if db_emp:
        # 仅更新非空字段（冗余字段可留空）
        if emp_schema.student_name:
            db_emp.student_name = emp_schema.student_name
        if emp_schema.class_name:
            db_emp.class_name = emp_schema.class_name
        db_emp.job_search_start_date = emp_schema.job_search_start_date
        db_emp.offer_received_date = emp_schema.offer_received_date
        db_emp.company_name = emp_schema.company_name
        db_emp.salary = emp_schema.salary
        db.commit()
        db.refresh(db_emp)
    return db_emp

# 删除就业信息
def delete_emp(db: Session, student_id: int):
    db_emp = get_id_emp(db, student_id=student_id)
    if db_emp:
        db.delete(db_emp)
        db.commit()
        return {"message": "删除成功"}
    return {"message": "删除失败，学生编号不存在"}


def get_top5_salary(db: Session):
    """查询薪资最高的前五名学生"""
    result = db.query(
        Emp.student_name,
        Emp.class_name,
        Emp.offer_received_date,
        Emp.company_name,
        Emp.salary
    ).order_by(Emp.salary.desc()).limit(5).all()

    return [{"姓名": name, "班级": cls, "就业时间": date, "公司": com, "薪资": sal}
            for name, cls, date, com, sal in result]


def get_employment_duration(db: Session):
    """统计每个学生的就业时长（天）"""
    # 提取日期差的天数（兼容多数数据库）
    result = db.query(
        Emp.student_id,
        Emp.student_name,
        func.extract('day', Emp.offer_received_date - Emp.job_search_start_date).label("duration_days")
    ).all()

    return [{"学生编号": id, "姓名": name, "就业时长(天)": int(days) if days else None}
            for id, name, days in result]


def get_avg_duration_by_class(db: Session):
    """统计每个班级的平均就业时长（天）"""
    # 先提取天数，再计算平均值
    duration_days = func.extract('day', Emp.offer_received_date - Emp.job_search_start_date)
    result = db.query(
        Emp.class_name,
        func.avg(duration_days).label("avg_duration")
    ).group_by(Emp.class_name).all()

    return [{"班级": cls, "平均就业时长(天)": round(avg, 2) if avg else None}
            for cls, avg in result]